﻿using System;
using System.Collections;
using System.Data;
using MySql.Data.MySqlClient;

namespace DMirrorMySQL
{
    public class DMirror
    {
        public string sc;
        private MySqlConnection con;
        public bool debugMode;
        private Hashtable sites;
        public readonly int POST_INI = 0, POST_TAGGED = 1, POST_THUMB_GOT = 2, POST_THUMB_SYNC = 4, POST_GOT = 8, POST_ITUNES = 16;

        private Hashtable taglist;// <key>tagname=dname,tag <value>tagid
        public DMirror(string sDBName, string sUserName, string sUserPWD, string sDBServer)//connect to db
        {
            MySqlConnectionStringBuilder sb = new MySqlConnectionStringBuilder();
            sb.Database = sDBName;
            sb.Server = sDBServer;
            sb.Port = 3306;
            sb.UserID = sUserName;
            sb.Password = sUserPWD;
            sc = sb.ConnectionString;
            con = new MySqlConnection(sc);
            con.Open();

            //check if exists
            DataTable t = new DataTable();
            MySqlDataAdapter od = new MySqlDataAdapter("show tables like 'taglist'", con);
            int k = od.Fill(t);
            if (k == 0)
            {
                initializeTables();

                od.SelectCommand.CommandText = "select site_id from sites";
                t = new DataTable();
                od.Fill(t);
                foreach (DataRow item in t.Rows)
                {
                    initializeSiteTables(item[0].ToString());
                }
            }
        }

        /*common*/

        public void getData(string sql, ref DataTable d)
        {
            //d.Dispose();
            d = new DataTable();
            MySqlDataAdapter a = new MySqlDataAdapter(sql, con);
            a.Fill(d);
        }

        /// <summary>
        /// initialize basic table structor
        /// </summary>
        public void initializeTables()
        {
            string[] iSqls ={
"drop table if exists tagList",
@"create table tagList(
 tag_id integer unsigned not null auto_increment comment 'ID of the Tag',
 tag_name varchar(255) comment 'tag name, should be only one across sites',
 parent_tag integer unsigned comment 'parent tag, if this is field defined, posts flagged with this tag would also gain parent tag',
 modified timestamp comment 'modified time',
 primary key(tag_id))
 ENGINE=InnoDB COMMENT='list of Tag names'  character set=utf8 collate utf8_unicode_ci",//tag list table
@"create index i_taglist on tagList(tag_name)",
@"drop table if exists sites",
@"create table sites(
 site_id smallint unsigned auto_increment comment 'site_id',
 site_url varchar(255) comment 'uri for danbooru post api',
 site_name varchar(255) comment 'display name for the site',
 site_posts bigint unsigned comment 'total post amount for the site',
 site_posts_synced bigint unsigned comment 'total synced post amount for the site',
 site_tags bigint unsigned comment 'total tag amount for the site',
primary key(site_id))ENGINE=InnoDB comment='site info'  character set=utf8 collate utf8_unicode_ci",//site list table
"insert into sites(site_url,site_name) values('danbooru.donmai.us','Danbooru')",
"insert into sites(site_url,site_name) values('konachan.com','Konachan')",
"insert into sites(site_url,site_name) values('oreno.imouto.org','IMoutou')"//beginner sites
                           };

            MySqlCommand c = con.CreateCommand();
            foreach (string item in iSqls)
            {
                c.CommandText = item;
                c.ExecuteNonQuery();
            }
        }

        public void initializeSiteTables(string site_id)
        {
            string[] createSQL = {
"drop table if exists posts_{0}",
"drop table if exists tags_{0}",
"drop table if exists comments_{0}",
@"create table posts_{0}(
 post_id bigint unsigned not null comment 'id for the post',
 md5 varchar(255) not null comment 'hash label for the post',
 height integer unsigned,
 width integer unsigned,
 theight integer unsigned,
 twidth integer unsigned,
 url varchar(500),
 url_thumb varchar(500),
 path_photo varchar(500),
 path_thumb varchar(500),
 size bigint unsigned ,
 status integer,
 modified timestamp,
 iphoto_id integer unsigned,
primary key (post_id))
ENGINE=InnoDB comment='post table of site{0}' character set =utf8
",//post table creation
"create index imd5_{0} on posts_{0}(md5)",
"create index idimension_{0} on posts_{0}(height,width)",
"create index istatus_{0} on posts_{0}(status)",
"create index imodified_{0} on posts_{0}(modified)",//index creation
@"create table tags_{0}(
post_id bigint unsigned ,
tag_id bigint unsigned,
modified timestamp,
primary key (post_id,tag_id))
ENGINE=InnoDB comment 'tag instance of site {0}'",
@"create table comments_{0}(
post_id bigint unsigned,
comment varchar(500),
author_id bigint unsigned,
modified timestamp,
primary key (post_id,modified,author_id))
ENGINE=InnoDB comment='user comments of site {0}'  character set=utf8 collate utf8_unicode_ci"//comment table creation
};
            MySqlCommand c = con.CreateCommand();
            foreach (string item in createSQL)
            {
                c.CommandText = string.Format(item, site_id);
                c.ExecuteNonQuery();
            }
        }

        public void initializeSiteTables(int site_id)
        {
            initializeSiteTables(site_id.ToString());
        }

        /*sites*/

        public DataTable initSites()
        {
            DataTable dlist = new DataTable();
            sites = new Hashtable();

            getData("select site_id,site_url,site_name from sites", ref dlist);

            foreach (DataRow item in dlist.Rows)
            {
                sites.Add(item[0].ToString(), item[1].ToString());
            }
            return dlist;
        }

        public string getSiteURL(string site_id)
        {
            if (sites.ContainsKey(site_id))
            {
                return (string)sites[site_id];
            }
            else
                return "-1";
        }

        /*posts*/

        /// <summary>
        /// 获取Post信息
        /// </summary>
        /// <param name="post_id">post 标志</param>
        /// <param name="site_id">site 标志</param>
        /// <returns></returns>
        public DPost getPostInfo(int post_id, int site_id)
        {
            DPost d = new DPost();
            DataTable dt = new DataTable();
            MySqlDataAdapter da = new MySqlDataAdapter(string.Format("select * from posts_{1} where post_id={0} ", post_id.ToString(), site_id.ToString()), con);
            da.Fill(dt);
            DataRow r = dt.Rows[0];
            Util.fillRowToDPost(ref r, ref d);
            r = null;
            dt = null;
            return d;
        }

        /// <summary>
        /// 批量同步Post状态
        /// </summary>
        /// <param name="site_id"></param>
        /// <param name="fileInfo"></param>
        public void bulkSyncPosts(int site_id, ref Hashtable fileInfo)
        {
            MySqlCommand cmd = new MySqlCommand("truncate table sync_temp", con);
            cmd.ExecuteNonQuery();
            MySqlTransaction ta = con.BeginTransaction();
            cmd = new MySqlCommand("", con, ta);
            /*insert all data into temp_table */
            foreach (string k in fileInfo.Keys)
            {
                cmd.CommandText = string.Format(@"
insert into sync_temp (post_id,size) select'{0}','{1}' from dual where not exists(select 1 from sync_temp where post_id='{0}')", k, (string)fileInfo[k]);
                cmd.ExecuteNonQuery();
            }
            ta.Commit();
            //正常部分
            cmd = new MySqlCommand(@"
update posts a set a.status='1',modified=null
where a.status='0'
and exists(select 1 from sync_temp b where b.post_id=a.post_id and b.size=a.size)", con);
            cmd.ExecuteNonQuery();
            //缺失部分
            cmd = new MySqlCommand(@"
update posts a set a.status='-1',modified=null
where a.status>1
and not exists(select 1 from sync_temp b where b.post_id=a.post_id)", con);
            cmd.ExecuteNonQuery();

            //不匹配部分
            cmd = new MySqlCommand(@"
update posts a set a.status='-2',modified=null
where exists(select 1 from sync_temp b where b.post_id=a.post_id and a.size <> b.size)", con);
            cmd.ExecuteNonQuery();
            cmd = null;
        }

        public void getPost(int site_id, ref DataTable results, string status)
        {
            MySqlDataAdapter da = new MySqlDataAdapter(string.Format(@"select * from posts_{0} where status in ({1})", site_id.ToString(), status), con);
            da.Fill(results);
            da = null;
        }

        /// <summary>
        /// 单独添加Post
        /// </summary>
        /// <param name="newpost"></param>
        public void addPost(DPost newpost)
        {
            MySqlCommand cmd = new MySqlCommand("", con);
            cmd.CommandText = string.Format(@"
insert IGNORE into posts_{1}  (post_id,md5,height,width,url,url_thumb,status,size,modified)
values ('{0}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',now())
", new string[] {
            newpost.PostID.ToString(),
            newpost.SiteID.ToString(),
            newpost.MD5,
            newpost.Height.ToString(),
            newpost.Width.ToString(),
            newpost.URL.Replace(newpost.MD5,"_MD5_"),
            newpost.tURL.Replace(newpost.MD5,"_MD5_"),
            newpost.Status.ToString(),
            newpost.Size.ToString()
            });

            /*
                        cmd.CommandText = string.Format(@"
            insert into posts_{1} (post_id,md5,height,width,url,url_thumb,status,size,modified)
            select '{0}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',null from dual
            where not exists(select 1 from posts where post_id='{0}') ", new string[]{
                        newpost.PostID.ToString(),
                        newpost.SiteID.ToString(),
                        newpost.MD5,
                        newpost.Height.ToString(),
                        newpost.Width.ToString(),
                        newpost.URL,
                        newpost.tURL,
                        newpost.Status.ToString(),
                        newpost.Size.ToString()
                       });*/
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                errorLog("newPost", cmd.CommandText, exp.Message);
            }
            cmd.Dispose();
        }

        /// <summary>
        /// 批量添加Post
        /// </summary>
        /// <param name="newpost"></param>
        //        public void addPost(ref DPost[] newpost)
        //        {
        //            MySqlTransaction ta = con.BeginTransaction();
        //            MySqlCommand cmd = new MySqlCommand("", con, ta);
        //            for (int i = 0; i < newpost.Length; i++)
        //            {
        //                if (newpost[i] == null || newpost[i].MD5 == "")
        //                    break;
        //                /*                cmd.CommandText =  string.Format(@"insert into posts_{1}
        //                                (post_id,md5,height,width,url,url_thumb,status,modified) values({0},{2},{3},{4},{5},{6},{7},now()) if not exists (select 1 from posts where post_id='{0}')", new string[]{
        //                            newpost[i].PostID.ToString(),
        //                            newpost[i].SiteID.ToString(),
        //                            newpost[i].MD5,
        //                            newpost[i].Height.ToString(),
        //                            newpost[i].Width.ToString(),
        //                            newpost[i].URL,
        //                            newpost[i].tURL,
        //                            newpost[i].Status.ToString()});*/

        //                cmd.CommandText = string.Format(@"insert IGNORE into posts_{1}
        //                (post_id,md5,height,width,url,url_thumb,status,modified) values({0},{2},{3},{4},{5},{6},{7},now())
        //                 ", new string[] {
        //                newpost[i].PostID.ToString(),
        //                newpost[i].SiteID.ToString(),
        //                newpost[i].MD5,
        //                newpost[i].Height.ToString(),
        //                newpost[i].Width.ToString(),
        //                newpost[i].URL,
        //                newpost[i].tURL,
        //                newpost[i].Status.ToString(),
        //                newpost[i].Size.ToString() });
        //                try
        //                {
        //                    cmd.ExecuteNonQuery();
        //                }
        //                catch (Exception exp)
        //                {
        //                    errorLog("newPost", cmd.CommandText, exp.Message);
        //                }
        //            }
        //            ta.Commit();
        //            ta = null;
        //            cmd.Dispose();
        //        }

        public string getSiteStatus(string site_id)
        {
            string r = "SiteName={0} Posts[Got={1}/Thumbed={2}/Total={3}] TagAssigned={4}";

            DataTable d = new DataTable();
            getData(string.Format(@"select
(select site_name from sites where site_id='{0}') ,
(select count(1) from posts_{0} where status={2}) ,
(select count(1) from posts_{0} where status={1}),
(select count(1) from posts_{0}) ,
(select count(1) from tags_{0})
from dual", site_id, POST_THUMB_GOT.ToString(), POST_GOT.ToString()), ref d);

            DataRow x = d.Rows[0];
            return string.Format(r, new string[] { x[0].ToString(), x[1].ToString(), x[2].ToString(), x[3].ToString(), x[4].ToString() });
        }

        public string getSiteStatus(int site_id)
        {
            return getSiteStatus(site_id.ToString());
        }

        /*Tags */

        /// <summary>
        /// 初始化Tag缓冲
        /// </summary>
        public void initTagList()
        {
            taglist = new Hashtable();
            taglist.Clear();
            MySqlDataAdapter a = new MySqlDataAdapter(@"
select tag_id,tag_name from tagList", con);
            DataTable dt = new DataTable();
            a.Fill(dt);
            a = null;
            foreach (DataRow r in dt.Rows)
            {
                taglist.Add(r["tag_name"].ToString(), r["tag_id"].ToString());
            }
            dt = null;
        }

        /// <summary>
        /// 根据Tag名称获取TagID
        /// </summary>
        /// <param name="tagName">tag名称</param>
        /// <returns>tagID</returns>
        public int
        getTagID(string otagName)
        {
            int id = -1;
            string tagName = otagName.Replace("\\", "~").Replace("\"", "\"\"");

            if (taglist.ContainsKey(tagName))
            {
                int.TryParse((string)taglist[tagName], out id);
            }
            return id;
        }

        /*

                /// <summary>
                /// 向TagList中新增一个Tag
                /// </summary>
                /// <param name="tagName">DanbooruTag名称</param>
                /// <param name="locale">本地化名称</param>
                /// <param name="localName">本地化语言</param>
                /// <returns>Tag标志</returns>
                public int insertTag(string tagName, string localName, string locale)
                {
                    int tagId = -1;
                    tagId = getTagID(tagName);
                    if (tagId == -1)//DanbooruTag不存在,添加新locale=raw的tag
                    {
                        MySqlCommand cmd = new MySqlCommand(string.Format(
        @"insert into tagList (tag_name,modified) values ('{0}','{1}')",
        tagName, Util.ToTimeStamp(DateTime.Now)), con);
                        try
                        {
                            int r = cmd.ExecuteNonQuery();
                            if (debugMode) errorLog("newTag", cmd.CommandText, r.ToString());
                            initTagList();
                            insertTag(tagName, localName, locale);
                        }
                        catch (Exception e)
                        {
                            errorLog("newTag", cmd.CommandText, e.Message);
                        }
                    }
                    else
                    {
                        MySqlCommand cmd = new MySqlCommand(string.Format(
        @"insert into tagLocale (tag_id,tag_name,locale,modified) values ('{0}','{1}','{2}','{3}')",new string[]{
        tagId.ToString(),localName,locale, Util.ToTimeStamp(DateTime.Now)}), con);
                        try
                        {
                            int r = cmd.ExecuteNonQuery();
                            if (debugMode) errorLog("localeTag", cmd.CommandText, r.ToString());
                        }
                        catch (Exception e)
                        {
                            errorLog("localeTag", cmd.CommandText, e.Message);
                        }
                    }

                    return tagId;
                }*/

        /// <summary>
        /// 添加新Tag
        /// </summary>
        /// <param name="tagName">Danbooru Tag</param>
        /// <returns></returns>
        public int newTag(string otagName)
        {
            int tagId = -1;
            // string tagName = otagName.Replace("'", @"\'");
            string tagName = otagName.Replace("\\", "~").Replace("\"", "\"\"");
            tagId = getTagID(tagName);
            if (tagId == -1)//DanbooruTag不存在,添加新locale=raw的tag
            {
                /*MySqlCommand cmd = new MySqlCommand(string.Format(
                @"insert into  taglist(tag_name,modified)
                select '{0}',null from dual
                where not exists(select * from taglist where tag_name = '{0}')",
                tagName), con);*/

                MySqlCommand cmd = new MySqlCommand(string.Format(
                "insert  into  taglist(tag_name,modified) select \"{0}\",now() from dual where not exists(select 1 from taglist where tag_name=\"{0}\")", tagName), con);
                int r = cmd.ExecuteNonQuery();
                //         if (debugMode) errorLog("newTag", cmd.CommandText, r.ToString());
                //initTagList();
                MySqlDataAdapter da = new MySqlDataAdapter(string.Format("select tag_id from taglist where tag_name = \"{0}\"", tagName), con);
                DataTable dt = new DataTable();
                da.Fill(dt);
                taglist.Add(tagName, dt.Rows[0]["tag_id"].ToString());
                dt.Dispose();
                da.Dispose();
                return getTagID(tagName);
            }
            return tagId;
        }

        /// <summary>
        /// 批量添加Tags
        /// </summary>
        /// <param name="tags">String数组，需要添加的tag</param>
        public void newTags(string[] tags)
        {
            string scmd = "insert IGNORE into  taglist(tag_name,modified) values(\"{0}\",now())";
            MySqlTransaction ta = con.BeginTransaction();
            MySqlCommand cmd = new MySqlCommand("", con, ta);
            try
            {
                //insert every tag
                for (int i = 0; i < tags.Length; i++)
                {
                    cmd.CommandText = string.Format(scmd, tags[i]);
                    cmd.ExecuteNonQuery();
                }
                //flush cache
                //initTagList();
            }
            catch (Exception ex)
            {
                errorLog("bulkNewTags", cmd.CommandText, ex.Message);
            }
            finally
            {
                ta.Commit();
            }
            ta = null;
            cmd = null;
        }

        public void updateTagLocale(int tagID, ref Hashtable localeList)
        {
        }

        public void cleanTag(int post_id, int site_id)
        {
            MySqlCommand cmd = new MySqlCommand(string.Format(@"
delete from  tags_{1} where  post_id='{0}'
", post_id.ToString(), site_id.ToString()), con);
            // errorLog("debug:insert_tag", cmd.CommandText, "");
            cmd.ExecuteNonQuery();
            cmd = null;
        }

        /// <summary>
        /// 设置tag
        /// </summary>
        /// <param name="post_id"></param>
        /// <param name="site_id"></param>
        /// <param name="tag_id"></param>
        public void assignTag(int post_id, int site_id, int tag_id)
        {
            /*MySqlCommand cmd = new MySqlCommand(string.Format(@"
            insert into tags (post_id,tag_id,site_id,modified)
            select '{0}','{1}','{2}' ,now() from dual
            where not exists(select * from tags where site_id='{2}' and post_id='{0}' and tag_id='{1}')
            ", post_id.ToString(), tag_id.ToString(), site_id.ToString()), con);*/
            MySqlCommand cmd = new MySqlCommand(string.Format(@"
insert  into tags_{2} (post_id,tag_id,modified)
select '{0}','{1}',now() from dual where not exists(select 1 from tags_{2} where post_id='{0}' and tag_id='{1}')
", post_id.ToString(), tag_id.ToString(), site_id.ToString()), con);
            // errorLog("debug:insert_tag", cmd.CommandText, "");
            cmd.ExecuteNonQuery();
            cmd = null;
        }

        public int assignTag(int post_id, int site_id, string tag_name)
        {
            int tag_id = getTagID(tag_name);
            if (tag_id == -1)
            {
                tag_id = newTag(tag_name);
                return 1;
            }
            assignTag(post_id, site_id, tag_id);
            return -1;
        }

        /// <summary>
        /// 批量增加
        /// </summary>
        /// <param name="tagName">存有tagName的string数组</param>
        public void bulkInsertTags(string[] tagName)
        {
        }

        /// <summary>
        /// 记录错误与日志
        /// </summary>
        /// <param name="method">操作</param>
        /// <param name="command">命令</param>
        /// <param name="result">结果</param>
        public void errorLog(string method, string command, string result)
        {
            Console.WriteLine(method + " " + result);
            //    MySqlTransaction ta = con.BeginTransaction();
            //  MySqlCommand cmd = new MySqlCommand(string.Format("insert into logs (modified,method,command,result) values(null,'{0}','{1}','{2}')",new string[]{method,command,result}),con,ta);
            // cmd.ExecuteNonQuery();
            // ta.Commit();
            // cmd = null;
        }

        public void syncThumb(ref string[] thumb, string site_id)
        {
            MySqlTransaction ta = con.BeginTransaction();
            MySqlCommand c = con.CreateCommand();
            c.CommandText = string.Format("drop table if exists in_thumb_{0}", site_id);
            c.ExecuteNonQuery();
            c.CommandText = string.Format("create table in_thumb_{0} (md5 varchar(500))  character set=utf8", site_id);
            c.ExecuteNonQuery();
            c.Transaction = ta;

            foreach (string item in thumb)
            {
                c.CommandText = string.Format("insert into in_thumb_{0}(md5) values('{1}')", site_id, item.Replace(".jpg", ""));
                c.ExecuteNonQuery();
            }

            //errorLog("init thumb import"+thumb.Length.ToString(), "", "success");
            ta.Commit(); ta.Dispose();
            c = con.CreateCommand();
            c.CommandText = string.Format("create index i_inthumb{0} on in_thumb_{0}(md5)", site_id);
            c.ExecuteNonQuery();

            c.CommandText = string.Format("update posts_{0} a set status=status+{1} where  status<{1} and exists (select 1 from in_thumb_{0} b where a.md5=b.md5) ", site_id, POST_THUMB_GOT.ToString());
            int z = c.ExecuteNonQuery();
            // c.CommandText = string.Format("drop table in_thumb_{0}", site_id); c.ExecuteNonQuery();
            c.Dispose();
            errorLog("site=" + site_id + " thumbnail_synced=" + z.ToString(), "", "Success");
        }

        public string[] getThumbDownList(string site_id)
        {
            DataTable d = new DataTable();
            getData(string.Format("select md5,url_thumb from posts_{0} where status <{1}", site_id, POST_THUMB_GOT.ToString()), ref d);

            string[] res = new string[d.Rows.Count - 1];

            for (int i = 0; i < d.Rows.Count - 1; i++)
            {
                res[i] = d.Rows[i][1].ToString().Replace("_MD5_", d.Rows[i][0].ToString());
            }

            return res;
        }
    }
}